The Formula Editor

OfficeReports contains a formula editor, which makes it easier to create formulas for derived variables. The formula editor can be used edit the formula for:

  • Categories in derived categorical variables
  • Derived numeric variables
  • Derived date variables

    •  

Formula Editor Form

 

Formulas for categories have to result in a boolean value (true or false)! If an expression resulting in a boolean value is used for a numerical derived variable, true will result in value '1' and false will result in value '0'.

 

Below we will explain how to reference categories and variables and which operators you can use:

 

Categories are referenced by separating the variable name and the category name with a dot, all of which is contained in square brackets [ ]. For example, [Gender.Male]

“!Observed” and “!Missing” can be used to reference all observations or all ‘missing’ observations for a variable:

[Gender.!Observed] and [Gender.!Missing]

!HasValue and !Value can be used to reference all observations for a variable where the observed category has a value. For example:

[Variable.!HasValue]

[Variable.!Value]

All variables but categorical variables can be referenced by the variable name. For example: [Age]

 

We can check all variables for being observed or missing:

[Variable.!Observedl] is =1 when true, and =0 when false

[Variable.!Missing] is =1 when true, and =0 when false

An arithmetic operator combines two operands arithmetically to produce an intermediate value. The operators are:

Addition (A + B gives the sum of A and B)

Subtraction (A - B gives the difference of A and B)

Multiplication (A * B multiples A by B)

Division (A / B divides A by B)

Exponentiation (A ^ B raises A to power of B)

Modulus Division (A % B gives the remainder of A divided by B)

A logical operator compares two operands or expressions and produces a true or false condition. There are two types of logical operators: conditional and Boolean. Conditional operators compare two values or expressions. Boolean operators connect string, numeric, or logical expressions together to determine true-false logic. You can combine Operators to create complex operators.

= Equal sign
< Less than
> Greater than
<> Not equal
!= Not equal
<= Less than or equal to
~> Not greater than
>= Greater than or equal to
NOT Boolean (logical) NOT
AND Boolean AND
OR Boolean OR
XOR Boolean exclusive OR

It is possible to let the expression give a result depending on a condition by using 'if'. It looks like if(condition, result if true, result if false).

Example: if([Age] > 50, [Discount]*2, [Discount]), which doubles the discount for those older than 50.

You can check the results by having a look at the data on respondent level in the 'Data View' tab. Select the variable you have created together with the variables you have used in the formula, so you can check if the result is what you expect for each respondent before switching to the Data View tab.